package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.codehaus.jettison.json.JSONException;
import org.json.simple.JSONObject;
import org.springframework.stereotype.Service;

import conpool.Pool;

@Service
public class CollectionData {
	
	//获取指定表tablename的前num条数据，并以list<JSONObject>形式返回。
	public List<JSONObject> dataToJson(String tablename,int num,Pool pool) throws SQLException, JSONException {

		List<JSONObject> list = new ArrayList<JSONObject>(); 
		Connection connection = pool.getConnection();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("select * from "+tablename+" limit "+num +"; ");
		
        ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据  
        int columnCount = md.getColumnCount();   //获得列数   
        while (rs.next()) {
        	JSONObject jsonObj = new JSONObject();  
        	
            // 遍历每一列  
            for (int i = 1; i <= columnCount; i++) {
                String columnName =md.getColumnLabel(i);  
                String value = rs.getString(columnName);  
                jsonObj.put(columnName, value);  
            }
            list.add(jsonObj);  
        }
        statement.close();
        pool.releaseConnection(connection);
        return list;
	}
	
	//执行拼装的SQL
	public List<JSONObject> dataToJson(String sqlString,Pool pool) throws SQLException, JSONException {

		List<JSONObject> list = new ArrayList<JSONObject>(); 
		Connection connection = pool.getConnection();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sqlString);
        ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据  
        int columnCount = md.getColumnCount();   //获得列数   
        while (rs.next()) {
        	JSONObject jsonObj = new JSONObject();  
            // 遍历每一列  
            for (int i = 1; i <= columnCount; i++) {
                String columnName =md.getColumnLabel(i);  
                String value = rs.getString(columnName);  
                jsonObj.put(columnName, value);  
            }
            list.add(jsonObj);  
        }
        statement.close();
        pool.releaseConnection(connection);
        return list;
	}
	
}
